const mysql = require('mysql2');
const tools = require('./tools')

class MysqlConnection {
    constructor(host, port, user, password, database) {
        this.connection = null;
        this.state = 0; // 0.未连接 1.正在连接 2.已连接 3.正在重连 4. 连接异常(也重连)
        this.host = host
        this.port = port
        this.user = user
        this.password = password
        this.database = database
    }

    handleConnect() {
        // console.log("this.host",this.host)
        // console.log("this.user",this.user)
        // console.log("this.password",this.password)
        // console.log("this.database",this.database)
        // console.log("this.port",this.port)
        this.connection = mysql.createConnection({
            host: this.host,
            user: this.user,
            password: this.password,
            database: this.database,
            port: this.port,
            typeCast: function (field, next) {
                if (field.type === 'DATETIME' || field.type === 'DATE' || field.type === 'TIMESTAMP') {
                    return field.string(); // 返回原始字符串
                }
                return next()
            }
        });

        this.connection.connect((err) => {
            if (!err) {
                this.state = 2
                console.log(new Date(), '✅ 成功连接到数据库 - threadId:', this.connection.threadId);
            }
        });

        // 监听错误事件
        this.connection.on('error', (err) => {
            this.state = 3
            if (err.code === 'ECONNREFUSED') {
                console.log(new Date(), '连接失败，正在尝试重新连接...');
            } else if (err.code === 'PROTOCOL_CONNECTION_LOST') {
                console.log(new Date(), '连接丢失，正在尝试重新连接...');
            } else if (err.code === 'ECONNRESET') {
                console.log(new Date(), '连接被重置，正在尝试重新连接...');
            } else {
                //   throw err;
                this.state = 4
                console.log(new Date(), "未知错误", err.code)
            }
            setTimeout(() => {
                this.handleConnect()
            }, 3000);
        })

        // 监听连接关闭事件
        this.connection.on('close', () => {
            console.warn(new Date(), '⚠️ 数据库连接已关闭，尝试重连...');
            setTimeout(() => {
                this.handleConnect()
            }, 3000);
        });


    }

    async point_save(data) {
        try {
            let record_list = JSON.parse(data)
            // console.log("record_list: ",record_list)
            if (record_list.length > 0 && this.connection && this.state === 2) {
                let sql_str = "INSERT INTO var_record_slave (project_id, addr, field_name, spec_type, value, create_time) VALUES ";
                let arr = []
                for (let record of record_list) {
                    arr.push(`(${record.project_id},'${record.addr}', '${record.field_name}', '${record.spec_type}', ${record.value}, '${record.create_time}')`)
                }
                let sql_str_part2 = arr.join(",")
                sql_str += sql_str_part2
                const [results] = await this.connection.promise().query(sql_str);
                return {
                    code: 0,
                    message: "success",
                    data: {
                        affect_rows: results.affectedRows
                    }
                }
            } else {
                return {
                    code: 1,
                    message: "sql连接状态异常",
                    data: {}
                }
            }
        } catch (error) {
            return {
                code: 1,
                message: String(error),
                data: {
                    info: "catch error",
                    detail: error
                }
            }
        }
    }

    point_list_v2_param_check(param) {
        if (typeof param !== 'object' || param === null || Array.isArray(param)) {
            return "参数异常";
        }
        const requiredFields = {
            page: 'number',
            size: 'number',
            addrs: 'string',
            interval: 'number',
            start_time: 'string',
            end_time: 'string'
        }
        for (const [field, expectedType] of Object.entries(requiredFields)) {
            if (!(field in param)) {
                return `缺少字段: ${field}`;
            }
            const actualType = typeof param[field];
            if (actualType !== expectedType) {
                return `字段 ${field} 类型错误: 期望 ${expectedType}, 实际 ${actualType}`;
            }
        }
        if (param.addrs === "") {
            return "字段 addr 不可为空"
        }

        if (!tools.isValidDateTime(param.start_time)) {
            return `字段 start_time 时间格式错误`;
        }
        if (!tools.isValidDateTime(param.end_time)) {
            return `字段 end_time 时间格式错误`;
        }

        param.page = parseInt(String(param.page))
        param.size = parseInt(String(param.size))
        param.interval = parseInt(String(param.interval))
        if (param.page <= 0) {
            param.page = 1
        }
        if (param.size <= 0) {
            param.size = 1
        }
        if (param.size > 50000) {
            param.size = 50000
        }
        if (param.interval <= 0) {
            param.interval = 1
        }
        return true;
    }

    async point_list(param) {
        try {
            let check_result = this.point_list_v2_param_check(param)
            if (check_result !== true) {
                return {
                    code: 2,
                    message: check_result,
                    data: {}
                }
            }
            let addr_arr = param.addrs.split(",")
            let tmp_arr = []
            for (let addr of addr_arr) {
                tmp_arr.push(`"${addr}"`)
            }
            let condition_addrs = tmp_arr.join(",")
            let start_page = (param.page - 1) * param.size
            let count_sql = `SELECT COUNT(*) FROM var_record_slave WHERE addr in (${condition_addrs}) AND create_time >= "${param.start_time}" AND create_time <= "${param.end_time}"`
            let data_sql = `SELECT * FROM var_record_slave WHERE addr in (${condition_addrs}) AND create_time >= "${param.start_time}" AND create_time <= "${param.end_time}" ORDER BY create_time DESC LIMIT ${start_page},${param.size}`
            // console.log("count_sql = ",count_sql)
            // console.log("data_sql = ",data_sql)
            if (this.connection && this.state === 2) {
                const [countResult] = await this.connection.promise().query(count_sql);
                const [dataResult] = await this.connection.promise().query(data_sql);
                return {
                    code: 0,
                    message: "success",
                    data: {
                        count: countResult[0]["COUNT(*)"],
                        list: dataResult,
                    }
                }
            } else {
                return {
                    code: 1,
                    message: "sql连接状态异常",
                    data: {}
                }
            }
        } catch (error) {
            // console.log(new Date(),"catch error",error)
            return {
                code: 1,
                message: String(error),
                data: {
                    info: "catch error",
                    detail: error
                }
            }
        }

    }

    async point_list_v2(param) {
        try {
            let check_result = this.point_list_v2_param_check(param)
            if (check_result !== true) {
                return {
                    code: 2,
                    message: check_result,
                    data: {}
                }
            }
            let addr_arr = param.addrs.split(",")
            let tmp_arr = []
            for (let addr of addr_arr) {
                tmp_arr.push(`"${addr}"`)
            }
            let condition_addrs = tmp_arr.join(",")
            let start_page = (param.page - 1) * param.size
            let data_sql = `SELECT * FROM var_record_slave WHERE addr in (${condition_addrs}) AND create_time >= "${param.start_time}" AND create_time <= "${param.end_time}" ORDER BY create_time ASC LIMIT ${start_page},${param.size}`
            if (this.connection && this.state === 2) {
                const [results] = await this.connection.promise().query(data_sql);
                // console.log(new Date(),"QUERY success: ",results);
                let points = {}
                /*--- 1. 生成动态列 ---*/
                let tableColumns = [{label: "时间", prop: "create_time"}]
                for (let index in addr_arr) {
                    let addr = addr_arr[index]
                    let field_name = "未知"
                    for (let row of results) {
                        if (row.addr === addr) {
                            field_name = row.field_name
                            break
                        }
                    }
                    tableColumns.push({
                        label: field_name,
                        prop: "value" + index
                    })
                    points[addr] = {
                        addr: addr,
                        field_name: field_name,
                        data: []
                    }
                }
                /*--- 2. 生成 xLine ---*/
                let xLine = []
                let t1 = new Date(param.start_time)
                let t2 = new Date(param.end_time)
                while (true) {
                    if (t1 > t2) {
                        break
                    }
                    xLine.push(tools.formatLocalDate(t1))
                    t1 = new Date(t1.getTime() + param.interval * 1000)
                }
                /*--- 3. 生成 tableData ---*/
                let tableData = []
                let list = results
                let blockStartIndex = 0 // 下次时段数据查询的起始位置
                for (let i = 0; i < xLine.length; i++) {
                    let tStart = xLine[i] // 当前时段的开始时间
                    let tEnd = ""         // 当前时段的结束时间
                    if (i === xLine.length - 1) {
                        tEnd = tools.formatLocalDate(new Date((new Date(tStart)).getTime() + param.interval * 1000))
                    } else {
                        tEnd = xLine[i + 1]
                    }
                    let tmpM = {}
                    tmpM["create_time"] = tStart
                    let blockData = []
                    for (let j = blockStartIndex; j < list.length; j++) {
                        if (list[j]["create_time"] < tStart) {
                            continue
                        }
                        if (list[j]["create_time"] >= tEnd) {
                            blockStartIndex = j
                            break
                        }
                        blockData.push(list[j])
                    }

                    for (let key in addr_arr) {
                        let addr = addr_arr[key]
                        let tmpV = 0
                        let matched = false
                        for (let row2 of blockData) {
                            if (row2.addr === addr) {
                                tmpV = row2.value
                                matched = true
                                break
                            }
                        }
                        if (!matched && points[addr].data.length > 0) {
                            tmpV = points[addr].data[points[addr].data.length - 1] // 当前时段没有数据, 则取上一次的值
                        }
                        points[addr].data.push(tmpV)
                        tmpM["value" + key] = tmpV
                    }
                    tableData.push(tmpM)
                }

                return {
                    code: 0,
                    message: "success",
                    data: {
                        tableData: tableData,
                        tableColumns: tableColumns
                    }
                }
            } else {
                return {
                    code: 1,
                    message: "sql连接状态异常",
                    data: {}
                }
            }
        } catch (error) {
            return {
                code: 1,
                message: String(error),
                data: {
                    info: "catch error",
                    detail: error
                }
            }
        }
    }

    async tendency_chart(param) {
        try {
            let check_result = this.point_list_v2_param_check(param)
            if (check_result !== true) {
                return {
                    code: 2,
                    message: check_result,
                    data: {}
                }
            }
            let addr_arr = param.addrs.split(",")
            let tmp_arr = []
            for (let addr of addr_arr) {
                tmp_arr.push(`"${addr}"`)
            }
            let condition_addrs = tmp_arr.join(",")
            let start_page = (param.page - 1) * param.size
            let data_sql = `SELECT * FROM var_record_slave WHERE addr in (${condition_addrs}) AND create_time >= "${param.start_time}" AND create_time <= "${param.end_time}" ORDER BY create_time ASC LIMIT ${start_page},${param.size}`
            if (this.connection && this.state === 2) {
                const [results] = await this.connection.promise().query(data_sql);
                // console.log(new Date(),"QUERY success: ",results);
                let points = {}
                /*--- 1. 生成动态列 ---*/
                for (let index in addr_arr) {
                    let addr = addr_arr[index]
                    let field_name = "未知"
                    for (let row of results) {
                        if (row.addr === addr) {
                            field_name = row.field_name
                            break
                        }
                    }
                    points[addr] = {
                        addr: addr,
                        field_name: field_name,
                        data: []
                    }
                }
                /*--- 2. 生成 xLine ---*/
                let xLine = []
                let t1 = new Date(param.start_time)
                let t2 = new Date(param.end_time)
                while (true) {
                    if (t1 > t2) {
                        break
                    }
                    xLine.push(tools.formatLocalDate(t1))
                    t1 = new Date(t1.getTime() + param.interval * 1000)
                }
                /*--- 3. 生成 points ---*/
                let list = results
                let blockStartIndex = 0 // 下次时段数据查询的起始位置
                for (let i = 0; i < xLine.length; i++) {
                    let tStart = xLine[i] // 当前时段的开始时间
                    let tEnd = ""         // 当前时段的结束时间
                    if (i === xLine.length - 1) {
                        tEnd = tools.formatLocalDate(new Date((new Date(tStart)).getTime() + param.interval * 1000))
                    } else {
                        tEnd = xLine[i + 1]
                    }
                    let tmpM = {}
                    tmpM["create_time"] = tStart
                    let blockData = []
                    for (let j = blockStartIndex; j < list.length; j++) {
                        if (list[j]["create_time"] < tStart) {
                            continue
                        }
                        if (list[j]["create_time"] >= tEnd) {
                            blockStartIndex = j
                            break
                        }
                        blockData.push(list[j])
                    }

                    for (let key in addr_arr) {
                        let addr = addr_arr[key]
                        let tmpV = 0
                        let matched = false
                        for (let row2 of blockData) {
                            if (row2.addr === addr) {
                                tmpV = row2.value
                                matched = true
                                break
                            }
                        }
                        if (!matched && points[addr].data.length > 0) {
                            tmpV = points[addr].data[points[addr].data.length - 1] // 当前时段没有数据, 则取上一次的值
                        }
                        points[addr].data.push(tmpV)
                        tmpM["value" + key] = tmpV
                    }
                }

                return {
                    code: 0,
                    message: "success",
                    data: {
                        points: points,
                        xLine: xLine
                    }
                }
            } else {
                return {
                    code: 1,
                    message: "sql连接状态异常",
                    data: {}
                }
            }
        } catch (error) {
            return {
                code: 1,
                message: String(error),
                data: {
                    info: "catch error",
                    detail: error
                }
            }
        }
    }

}

const mysqlCon = new MysqlConnection()

module.exports = {
    mysqlCon
};
